﻿/******************************
Copyright (c) 2009, Avanade
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

* Neither the name of Avanade nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS 
FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, 
BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 
LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
******************************/

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Data;

namespace PhpSiteGenerator
{
    /// <summary>
    /// Handles logic for creating a Silverlight UI and PHP backed site based on the given DatabaseInfo object
    /// </summary>
    public static class TSSLSiteGenerator
    {
        #region common variables

        private static string jsonCreate = "{0}: getTextValue(sender, 'create_txt{1}')";
        private static string jsonData = "{0}: dataParams[{1}]";
        private static string XAMLTemplatesDir = "XAMLTemplatesForTableStorage";
        private static string SiteTemplatesDir = "SiteTemplatesForTableStorage";

        #endregion

        public static void GenerateSite(DatabaseInfo database)
        {
            TSSiteGenerator.GenerateDatabasePage(database);
            GenerateDataAccessPage(database);
            GenerateCRUDLib(database);

            TSSiteGenerator.GenerateStaticPage(database, XAMLTemplatesDir, "index.php");
            TSSiteGenerator.GenerateStaticPage(database, XAMLTemplatesDir, "Silverlight.js");
            TSSiteGenerator.GenerateStaticPage(database, XAMLTemplatesDir, "jquery-1.2.6.js");
            TSSiteGenerator.GenerateStaticPage(database, SiteTemplatesDir, "utilities.php");

            // Based on user input decide which select page to output
            // There are four options/templates sortingandpaging, paging, sorting, and none
            if (database.EnablePaging && database.EnableSorting)
            {
                GenerateTemplateWithPagingAndSorting(database);
            }
            else if (database.EnableSorting)
            {
                GenerateTemplateWithSorting(database);
            }
            else if (database.EnablePaging)
            {
                GenerateTemplateWithPaging(database);
            }
            else
            {
                GenerateTemplate(database);
            }

            TSSiteGenerator.Copy(Environment.CurrentDirectory + "\\Microsoft", database.SaveFilesDir + "\\" + "Microsoft");

        }

        /// <summary>
        /// Creates the dataaccess.php file and saves it to the filesystem
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateDataAccessPage(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader dbFileReader = new StreamReader(Path.Combine(XAMLTemplatesDir, "dataaccess.php.txt"));
            StreamWriter dbFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "dataaccess.php"));

            try
            {
                //Read the file into a string
                string file = dbFileReader.ReadToEnd();

                // build validation logic
                validationLogic = TSSiteGenerator.BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{IS_VALID}", "false");
                    file = file.Replace("{DATE_VALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{IS_VALID}", "true");
                    file = file.Replace("{DATE_VALIDATION}", "");
                }

                // Replace tags in the file with configured data
                file = file.Replace("{UPDATE_QUERY}", TSSiteGenerator.BuildUpdateQuery(database));
                file = file.Replace("{INSERT_QUERY}", TSSiteGenerator.BuildInsertQuery(database));
                file = file.Replace("{DELETE_QUERY}", TSSiteGenerator.BuildDeleteQuery(database));
                file = file.Replace("{PK_COLUMN}", database.PKColumn.ToString());
                file = file.Replace("{DATA_PARAMS}", TSSiteGenerator.BuildDataParameters(database));

                //Replace tags in the file with configured data
                file = file.Replace("{ASSIGNPROPERTIES}", AssignProperties(database));

                //Write the file to disk
                dbFileWriter.Write(file);

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                dbFileReader.Close();
                dbFileWriter.Close();
            }

        }

        /// <summary>
        /// Creates the crudLib.js file which contains Javascript logic for user interaction with the Silverlight control 
        /// and saves it to the filesystem
        /// </summary>
        /// <param name="database">The database configuration</param>
        private static void GenerateCRUDLib(DatabaseInfo database)
        {
            StreamReader crudLibFileReader = new StreamReader(Path.Combine(XAMLTemplatesDir, "crudLib.js.txt"));
            StreamWriter crudLibFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "crudLib.js"));

            try
            {
                // Read the file into a string
                string file = crudLibFileReader.ReadToEnd();

                // Replace tags in the text file with configured data
                file = file.Replace("{COLUMN_COUNT}", (database.Columns.Count).ToString());
                file = file.Replace("{TOTAL_ROWS}", "0"); //GetRowCount(database).ToString());
                file = file.Replace("{ROW_COUNT}",database.NumberOfRowsPerPage.ToString());
                file = file.Replace("{PAGING_ENABLED}", (database.EnablePaging) ? "true" : "false" );
                file = file.Replace("{COLUMN_NAMES}", BuildColumnNamesString(database));
                file = file.Replace("{DEFAULT_SORT}", database.PKColumn.ToString());
                file = file.Replace("{QUERY_STRING}", BuildQueryString(database));
                file = file.Replace("{JSON_CREATE_PARAMS}", BuildJSONParams(database, jsonCreate, 1));
                file = file.Replace("{JSON_DATA_PARAMS}", BuildJSONParams(database, jsonData, 0));
                file = file.Replace("{PK_COLUMN}", database.PKColumn.ToString());

                // Write the file to disk
                crudLibFileWriter.Write(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                crudLibFileReader.Close();
                crudLibFileWriter.Close();
            }
        }

        private static void GenerateTemplateWithPagingAndSorting(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader templateFileReader = new StreamReader(Path.Combine(XAMLTemplatesDir, "templateWithPagingAndSorting.php.txt"));
            StreamWriter templateFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "template.php"));

            try
            {
                // Read the file into a string
                string file = templateFileReader.ReadToEnd();

                // build validation logic
                validationLogic = TSSiteGenerator.BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{IS_VALID}", "false");
                    file = file.Replace("{DATE_VALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{IS_VALID}", "true");
                    file = file.Replace("{DATE_VALIDATION}", "");
                }

                // Replace tags in the text file with configured data
                file = file.Replace("{COLUMN_COUNT}", (database.Columns.Count).ToString());
                file = file.Replace("{ROW_COUNT}", database.NumberOfRowsPerPage.ToString());
                file = file.Replace("{PK_COLUMN}", database.PKColumn.ToString());
                file = file.Replace("{DEFAULT_SORT}", database.PKColumn.ToString());
                file = file.Replace("{TABLE_NAME}", database.AzureTable.ToString());
                file = file.Replace("{SORT_VALIDATION}", TSSiteGenerator.BuildValidationString(database));
                file = file.Replace("{SELECT_QUERY}", TSSiteGenerator.BuildSelectQuery(database));
                file = file.Replace("{HEADER_ROW}", BuildHeaderRow(database));
                file = file.Replace("{DATA_ROW}", BuildDataRow(database));
                file = file.Replace("{CREATE_ROW}", BuildCreateRow(database));

                // Write the file to disk
                templateFileWriter.Write(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                templateFileReader.Close();
                templateFileWriter.Close();
            }

        }

        private static void GenerateTemplateWithPaging(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader templateFileReader = new StreamReader(Path.Combine(XAMLTemplatesDir, "templateWithPaging.php.txt"));
            StreamWriter templateFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "template.php"));

            try
            {
                // Read the file into a string
                string file = templateFileReader.ReadToEnd();

                // build validation logic
                validationLogic = TSSiteGenerator.BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{IS_VALID}", "false");
                    file = file.Replace("{DATE_VALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{IS_VALID}", "true");
                    file = file.Replace("{DATE_VALIDATION}", "");
                }

                // Replace tags in the text file with configured data
                file = file.Replace("{COLUMN_COUNT}", (database.Columns.Count).ToString());
                file = file.Replace("{ROW_COUNT}", database.NumberOfRowsPerPage.ToString());
                file = file.Replace("{PK_COLUMN}", database.PKColumn.ToString());
                file = file.Replace("{TABLE_NAME}", database.AzureTable.ToString());
                file = file.Replace("{SELECT_QUERY}", TSSiteGenerator.BuildSelectQuery(database));
                file = file.Replace("{HEADER_ROW}", BuildHeaderRow(database));
                file = file.Replace("{DATA_ROW}", BuildDataRow(database));
                file = file.Replace("{CREATE_ROW}", BuildCreateRow(database));

                // Write the file to disk
                templateFileWriter.Write(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                templateFileReader.Close();
                templateFileWriter.Close();
            }

        }

        private static void GenerateTemplateWithSorting(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader templateFileReader = new StreamReader(Path.Combine(XAMLTemplatesDir, "templateWithSorting.php.txt"));
            StreamWriter templateFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "template.php"));

            try
            {
                // Read the file into a string
                string file = templateFileReader.ReadToEnd();

                // build validation logic
                validationLogic = TSSiteGenerator.BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{IS_VALID}", "false");
                    file = file.Replace("{DATE_VALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{IS_VALID}", "true");
                    file = file.Replace("{DATE_VALIDATION}", "");
                }

                // Replace tags in the text file with configured data
                file = file.Replace("{COLUMN_COUNT}", (database.Columns.Count).ToString());
                file = file.Replace("{DEFAULT_SORT}", database.PKColumn.ToString());
                file = file.Replace("{TABLE_NAME}", database.AzureTable.ToString());
                file = file.Replace("{SORT_VALIDATION}", TSSiteGenerator.BuildValidationString(database));
                file = file.Replace("{PK_COLUMN}", database.PKColumn.ToString());
                file = file.Replace("{SELECT_QUERY}", TSSiteGenerator.BuildSelectQuery(database));
                file = file.Replace("{HEADER_ROW}", BuildHeaderRow(database));
                file = file.Replace("{DATA_ROW}", BuildDataRow(database));
                file = file.Replace("{CREATE_ROW}", BuildCreateRow(database));

                // Write the file to disk
                templateFileWriter.Write(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                templateFileReader.Close();
                templateFileWriter.Close();
            }

        }

        private static void GenerateTemplate(DatabaseInfo database)
        {
            // date validation reference
            string validationLogic = null;

            StreamReader templateFileReader = new StreamReader(Path.Combine(XAMLTemplatesDir, "template.php.txt"));
            StreamWriter templateFileWriter = new StreamWriter(Path.Combine(database.SaveFilesDir, "template.php"));

            try
            {
                // Read the file into a string
                string file = templateFileReader.ReadToEnd();

                // build validation logic
                validationLogic = TSSiteGenerator.BuildDateValidationLogic(validationLogic, database);

                // set $valid variable according to whether or not validation will be done
                if (!string.IsNullOrEmpty(validationLogic))
                {
                    file = file.Replace("{IS_VALID}", "false");
                    file = file.Replace("{DATE_VALIDATION}", validationLogic);
                }
                else
                {
                    file = file.Replace("{IS_VALID}", "true");
                    file = file.Replace("{DATE_VALIDATION}", "");
                }

                // Replace tags in the text file with configured data
                file = file.Replace("{COLUMN_COUNT}", (database.Columns.Count).ToString());
                file = file.Replace("{TABLE_NAME}", database.AzureTable );
                file = file.Replace("{PK_COLUMN}", database.PKColumn.ToString());
                file = file.Replace("{SELECT_QUERY}", TSSiteGenerator.BuildSelectQuery(database));
                file = file.Replace("{HEADER_ROW}", BuildHeaderRow(database));
                file = file.Replace("{DATA_ROW}", BuildDataRow(database));
                file = file.Replace("{CREATE_ROW}", BuildCreateRow(database));

                // Write the file to disk
                templateFileWriter.Write(file);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                templateFileReader.Close();
                templateFileWriter.Close();
            }

        }

        #region Helper Methods

        /// <summary>
        /// Builds a string representing array values for the column names
        /// </summary>
        /// <param name="database">A configured DatabaseInfo object</param>
        /// <example>'column1', 'column2', 'column3', 'column4', 'column5'</example>
        private static string BuildColumnNamesString(DatabaseInfo database)
        {
            StringBuilder arrayValues = new StringBuilder();
            string colName = "'{0}'";
            int colCounter = 0;

            // for each column selected in the table, add to the string
            foreach (TableColumn column in database.Columns)
            {
                // do not add the primary key
                if (column.ColumnName != database.PKColumn)
                {
                    // add comma after each param
                    if (colCounter != 0)
                    {
                        arrayValues.Append(", ");
                    }

                    arrayValues.Append(string.Format(colName, column.ColumnName));

                    // increment colCounter
                    colCounter++;
                }
            }

            return arrayValues.ToString();
        }

        /// <summary>
        /// Builds a query string based on user selected options for paging, sorting, paging and sorting or none of those options
        /// </summary>
        /// <param name="database">A configured DatabaseInfo object</param>
        /// <example>qStr = qStr + '?sort=' + sort + '&pageNum=' + pageNum;</example>
        private static string BuildQueryString(DatabaseInfo database)
        {
            string queryString = "";

            // create query string based on user selected options
            if (database.EnablePaging && database.EnableSorting)
            {
                queryString = "qStr = qStr + '?sort=' + sort + '&pageNum=' + pageNum;";
            }
            else if (database.EnableSorting)
            {
                queryString = "qStr = qStr + '?sort=' + sort;";
            }
            else if (database.EnablePaging)
            {
                queryString = "qStr = qStr + '?pageNum=' + pageNum";
            }
            else
            {
                // return empty, replace {QUERY_STRING} in js text file with nothing, JS function will return the URL only
                queryString = "";
            }

            return queryString;
        }

        /// <summary>
        /// Builds a string representing JSON formatted array values for an AJAX operation
        /// </summary>
        /// <param name="database">A configured DatabaseInfo object</param>
        /// <param name="paramFormat">String representing the format for each parameter</param>
        /// <param name="startVal">An integer representing either 0 or 1 depending on whether the JS function needs to access an array or index location</param>
        /// <example>column1: getTextValue(sender, 'create_txt1'), column2: getTextValue(sender, 'create_txt2'), ...</example>
        private static string BuildJSONParams(DatabaseInfo database, string paramFormat, int startVal)
        {
            StringBuilder jsonParams = new StringBuilder();
            string param = paramFormat;

            // for each column, not including the primary key, fill in data
            int colCounter = startVal;

            foreach (TableColumn column in database.Columns)
            {
                // Do not update the primary key column
                if (column.ColumnName != database.PKColumn)
                {
                    // Only add comma after the first column
                    if (colCounter != startVal)
                    {
                        jsonParams.Append(", ");
                    }

                    jsonParams.Append(string.Format(param,column.ColumnName, colCounter.ToString()));
                    colCounter++;
                }
                
            }

            return jsonParams.ToString();
        }

        /// <summary>
        /// Builds a string representing the header row for the Silverlight control
        /// </summary>
        /// <param name="database">A configured DatabaseInfo object</param>
        /// <returns>string of XAML for the header row</returns>
        /// <example><TextBlock x:Name="column name" Grid.Row="0" Grid.Column="0" Margin="3" Height="Auto" Width="Auto" Text="Title" Foreground="#FF333333" MouseLeftButtonDown="handleSortMouseClick" MouseEnter="handleMouseEnter" MouseLeave="handleMouseLeave" /></example>
        private static string BuildHeaderRow(DatabaseInfo database)
        {
            StringBuilder headerRow = new StringBuilder();
            string txtBlock = "";

            // determine which template to use based on whether sorting functionality is enabled
            if (database.EnableSorting)
            {
                // txtBlock = "<TextBlock x:Name=\"{0}\" Height=\"<?php echo $txtHeight ?>\" Width=\"<?php echo $txtWidth ?>\" Canvas.Top=\"<?php echo $margin ?>\" Canvas.Left=\"<?php echo $txtCanvasLeft{1}; ?>\" Text=\"{0}\" Foreground=\"#FF333333\" FontWeight=\"Bold\" FontFamily=\"Verdana\" FontSize=\"10\" MouseLeftButtonDown=\"handleSortMouseClick\" MouseEnter=\"handleMouseEnter\" MouseLeave=\"handleMouseLeave\"/>";
                txtBlock = "<TextBlock x:Name=\"{0}\" Grid.Row=\"0\" Grid.Column=\"{1}\" Margin=\"3\" Height=\"Auto\" Width=\"Auto\" Text=\"{0}\" Foreground=\"#FF333333\" MouseLeftButtonDown=\"handleSortMouseClick\" MouseEnter=\"handleMouseEnter\" MouseLeave=\"handleMouseLeave\" />";
            }
            else
            {
                // txtBlock = "<TextBlock x:Name=\"{0}\" Height=\"<?php echo $txtHeight ?>\" Width=\"<?php echo $txtWidth ?>\" Canvas.Top=\"<?php echo $margin ?>\" Canvas.Left=\"<?php echo $txtCanvasLeft{1}; ?>\" Text=\"{0}\" Foreground=\"#FF333333\" FontWeight=\"Bold\" FontFamily=\"Verdana\" FontSize=\"10\" />";
                txtBlock = "<TextBlock x:Name=\"{0}\" Grid.Row=\"0\" Grid.Column=\"{1}\" Margin=\"3\" Height=\"Auto\" Width=\"Auto\" Text=\"{0}\" Foreground=\"#FF333333\" />";
            }

            int counter = 0;

            // for each selected column in the table, create a textblock with the appropriate names and append to the headerRow string
            foreach (TableColumn column in database.Columns)
            {
                // do not display the Primary Key column
                if (column.ColumnName != database.PKColumn)
                {
                    // if this is not the first, append a newline to the output to make it neat
                    if (counter!=0)
                    {
                        headerRow.Append(Environment.NewLine);
                    }

                    headerRow.Append(string.Format(txtBlock, column.ColumnName, counter));

                    counter++;
                }

                
            }

            return headerRow.ToString();
        }

        /// <summary>
        /// Builds a string representing the data row for the Silverlight control
        /// </summary>
        /// <param name="database">A configured DatabaseInfo object</param>
        /// <returns>string of XAML representing the columns of a record</returns>
        /// <example><TextBox x:Name="<?php echo 'row' . $i .'_txt1'; ?>" Tag="0" Grid.Row="<?php echo $i ?>" Grid.Column="0" Background="<?php echo $rowColor ?>" Foreground="#FF333333" Margin="3" Height="Auto" Width="Auto" Text="Column1" GotFocus="handleGotFocus" LostFocus="handleLostFocus" /></example>
        private static string BuildDataRow(DatabaseInfo database)
        {
            StringBuilder dataRow = new StringBuilder();

            string txtBox = "<TextBox x:Name=\"<?php echo 'row' . $i .'_txt{0}'; ?>\" Tag=\"{1}\" Grid.Row=\"<?php echo $i ?>\" Grid.Column=\"{1}\" Background=\"<?php echo $rowColor ?>\" Foreground=\"#FF333333\" Margin=\"3\" Height=\"Auto\" Width=\"Auto\" Text=\"<?php echo html_entity_decode($entity->{2}{3},ENT_COMPAT,'iso-8859-1')?>\" GotFocus=\"handleGotFocus\" LostFocus=\"handleLostFocus\" />";
            string txtBoxWithDate = "<TextBox x:Name=\"<?php echo 'row' . $i .'_txt{0}'; ?>\" Tag=\"{1}\" Grid.Row=\"<?php echo $i ?>\" Grid.Column=\"{1}\" Background=\"<?php echo $rowColor ?>\" Foreground=\"#FF333333\" Margin=\"3\" Height=\"Auto\" Width=\"Auto\" Text=\"<?php echo html_entity_decode((isset($row['{2}']) ? $row['{2}']{3} : \"\"),ENT_COMPAT,'iso-8859-1')?>\" GotFocus=\"handleGotFocus\" LostFocus=\"handleLostFocus\" />";
     
            string dateTime = "";

            int rowCounter = 1;
            int columnCounter = 0;

            // create a TextBox for each column selected in the table with appropriate parameters
            foreach (TableColumn column in database.Columns)
            {
                // do not display the Primary Key Column
                if (column.ColumnName != database.PKColumn)
                {
                    if (rowCounter != 1)
                    {
                        dataRow.Append(Environment.NewLine);
                    }

                    // if this column / field is a dateimte, format output
                    if (column.ColumnType == "datetime")
                    {
                        dateTime = "->format('Y-m-d H:i:s')";
                        dataRow.Append(string.Format(txtBoxWithDate, rowCounter.ToString(), columnCounter.ToString(), column.ColumnName, dateTime));
                    }
                    else
                    {
                        dataRow.Append(string.Format(txtBox, rowCounter.ToString(), columnCounter.ToString(), column.ColumnName, dateTime));
                    }
  
             

                    rowCounter++;
                    columnCounter++;
                }
            }

            return dataRow.ToString();
        }

        /// <summary>
        /// Builds a string representing the create row for the Silverlight control
        /// </summary>
        /// <param name="database">A configured DatabaseInfo object</param>
        /// <returns>string of XAML representing columns of a record</returns>
        private static string BuildCreateRow(DatabaseInfo database)
        {
            StringBuilder createRow = new StringBuilder();

            string txtBox = "<TextBox x:Name=\"create_txt{0}\" Tag=\"{0}\" Grid.Row=\"1\" Grid.Column=\"{1}\" Margin=\"3\" Height=\"Auto\" Width=\"Auto\" MinWidth=\"150\" Foreground=\"<?php echo $defaultTextColor ?>\" Background=\"<?php echo $rowColor ?>\" Text=\"{2}\" FontStyle=\"Italic\" GotFocus=\"handleCreateTextGotFocus\" LostFocus=\"handleCreateTextLostFocus\" />";

            int columnCounter = 0;
            int rowCounter = 1;

            // create a TextBox for each column selected in the table with default text indicating the column name
            foreach (TableColumn column in database.Columns)
            {
                // do not display the Primary Key Column
                if (column.ColumnName != database.PKColumn)
                {
                    if (rowCounter != 1)
                    {
                        createRow.Append(Environment.NewLine);
                    }

                    createRow.Append(string.Format(txtBox, rowCounter.ToString(), columnCounter.ToString(), column.ColumnName));

                    rowCounter++;
                    columnCounter++;
                }
            }

            return createRow.ToString();
        }

        private static int GetRowCount(DatabaseInfo database)
        {
            int rowCount;
 
            // connect to db to determine the number of rows for the selected DB and table
            SqlConnection conn = new SqlConnection(String.Format("server={0};uid={1};pwd={2};Initial Catalog={3}", database.ServerName, database.UserName, database.Password, database.DatabaseName));

            try
            {
                //Open the connection
                conn.Open();

                //First determine the primary key for this table, if one doesn't exist, display error
                SqlCommand getRowCountCommand = new SqlCommand();
                getRowCountCommand.Connection = conn;
                getRowCountCommand.CommandType = CommandType.Text;
                string rowCountCommand = @"select count(*) FROM {0}";

                getRowCountCommand.CommandText = String.Format(rowCountCommand, database.TableName);

                rowCount = (int)getRowCountCommand.ExecuteScalar();

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }

            return rowCount;
        }

        /// <summary>
        /// Assign Properties to Entities
        /// </summary>
        /// <param name="database">The database configuration</param>
        /// <returns>Example: "$zentites->prop = $_GET["prop"];</returns>
        public static string AssignProperties(DatabaseInfo database)
        {
            //Baseline update
            string statement = @"$zentity->{0} = $_GET[""{0}""];";

            //Create each column update based on selected columns
            StringBuilder updateColumns = new StringBuilder();

            foreach (TableColumn column in database.Columns)
            {
                //Do not update the primary key column
                if (column.ColumnName != database.PKColumn)
                {
                    //Add column to column string
                    updateColumns.Append(string.Format(statement, column.ColumnName));
                    updateColumns.Append(Environment.NewLine);
                }
            }

            return updateColumns.ToString();

        }


        #endregion
    }
}
